import openpyxl


def get_sheet(file_name):
    book = openpyxl.load_workbook(file_name)
    sheet = book[book.sheetnames[0]]
    return sheet


def get_column(sheet, column):
    data = list()
    for cell in sheet[column]:
        data.append(cell.value)
    return data


def get_test_data(sheet, column):
    data1 = get_column(sheet, column[0])
    data2 = get_column(sheet, column[1])
    data3 = get_column(sheet, column[2])
    data = zip(data1, data2, data3)
    averages = list()
    for i in data:
        averages.append(i)

    averages.pop(0)

    return averages


def average(repeated_data_list):
    avg = list()
    for i in repeated_data_list:
        avg.append((i[0] + i[1] + i[2]) // 3)
    return avg


def get_average_data():
    sheet = get_sheet('phylum.xlsx')
    CK_0_1_data = average(get_test_data(sheet, ('B', 'E', 'H')))
    CK_0_3_data = average(get_test_data(sheet, ('C', 'F', 'I')))
    CK_0_6_data = average(get_test_data(sheet, ('D', 'G', 'J')))
    HU_100_1_data = average(get_test_data(sheet, ('K', 'N', 'Q')))
    HU_100_3_data = average(get_test_data(sheet, ('L', 'O', 'R')))
    HU_100_6_data = average(get_test_data(sheet, ('M', 'P', 'S')))
    TH_1000_1_data = average(get_test_data(sheet, ('T', 'W', 'Z')))
    TH_1000_3_data = average(get_test_data(sheet, ('U', 'X', 'AA')))
    TH_1000_6_data = average(get_test_data(sheet, ('V', 'Y', 'AB')))
    TT_2000_1_data = average(get_test_data(sheet, ('AC', 'AF', 'AI')))
    TT_2000_3_data = average(get_test_data(sheet, ('AD', 'AG', 'AJ')))
    TT_2000_6_data = average(get_test_data(sheet, ('AE', 'AH', 'AK')))

    return (CK_0_1_data, CK_0_3_data, CK_0_6_data, HU_100_1_data, HU_100_3_data, HU_100_6_data,
            TH_1000_1_data, TH_1000_3_data, TH_1000_6_data, TT_2000_1_data, TT_2000_3_data, TT_2000_6_data)


def get_phylum():
    sheet = get_sheet('phylum.xlsx')
    phylums = list()
    for i in sheet['A']:
        phylums.append(i.value)
    phylums.pop(0)
    return phylums

    # print(CK_0_1_data)
    # print(CK_0_3_data)
    # print(CK_0_6_data)
    # print()
    # print(HU_100_1_data)
    # print(HU_100_3_data)
    # print(HU_100_6_data)
    # print()
    # print(TH_1000_1_data)
    # print(TH_1000_3_data)
    # print(TH_1000_6_data)
    # print()
    # print(TT_2000_1_data)
    # print(TT_2000_3_data)
    # print(TT_2000_6_data)
    # print(len(HU_100_1_data))


if __name__ == "__main__":
    sheet = get_sheet('phylum.xlsx')
    data = get_average_data()[0]
    print(data)
